unit DbHelper;


interface
 
uses
  Uni,classes,Generics.Collections,SQLiteUniProvider;

type
  TDbHelper=class
    constructor Create(connectionstring:string;AOwner:TComponent);
    destructor Destory;
  private
    _connection:TUniConnection;
    _tran:TUniTransaction;
    _aowner:TComponent;
    _query:TUniQuery;
    SQLiteUniProvider1: TSQLiteUniProvider;
    procedure initQuery;
  public
    procedure GetSqlStrDataSet(query:TUniQuery;sql:string;param:TDictionary<string,string>);
    function SetSqlStrNonQuery(sql:string;param:TDictionary<string,string>):integer;overload;
    function SetSqlStrNonQuery(sql:string;return:boolean;param:TDictionary<string,string>):integer;overload;
    property Connection:TUniConnection read _connection;
    procedure StartTran;
    procedure CommitTran;
  end;

implementation
uses
  sysutils;

{ TDbHelper }

procedure TDbHelper.CommitTran;
begin
  if _connection.Connected then
    _tran.Commit;
end;

constructor TDbHelper.Create(connectionstring:string;AOwner:TComponent);
begin
  _aowner:=aowner;
  _connection:=TUniConnection.Create(_aowner);
  _connection.ProviderName:='sqlite';
  _connection.Database:=connectionstring;
  _query:=tuniquery.Create(_aowner);
  _tran:=TUniTransaction.Create(_aowner);
  _tran.DefaultConnection:=_connection;

end;

destructor TDbHelper.Destory;
begin
  _connection.Close;
  _connection.Free;
  _tran.Free;
  freeandnil(_query);
end;

procedure TDbHelper.GetSqlStrDataSet(query:TUniQuery;sql:string;param:TDictionary<string,string>);
var
  k:string;
begin
  if not assigned(query) then
  begin
    query:=TUniQuery.Create(_aowner);
  end;
  //if not query.Active then
  query.Active:=false;
  query.Filter:='';
  query.Connection:=_connection;
  query.SQL.Clear;
  query.SQL.Append(sql);
  if assigned(param) then
  begin
  for k in param.Keys do
  begin
    query.ParamByName(k).Value:=param[k];
  end;
  end;
    query.Prepare;
    query.Active:=true;
    param.free;
end;

procedure TDbHelper.initQuery;
begin
  if not assigned(_query) then
  _query:=tuniquery.Create(_aowner);
  if not _query.Active then
  _query.Connection:=_connection;
  _query.Active:=false;
end;

function TDbHelper.SetSqlStrNonQuery(sql: string;param:TDictionary<string,string>): integer;
begin
  SetSqlStrNonQuery(sql,false,param);
end;

function TDbHelper.SetSqlStrNonQuery(sql: string; return: boolean;param:TDictionary<string,string>): integer;
var
  k:string;
begin
  initQuery;
  _query.SQL.Clear;
  _query.SQL.Append(sql);
  if assigned(param) then
  begin
  for k in param.Keys do
  begin
    _query.ParamByName(k).Value:=param[k];
  end;
  end;
    _query.Prepare;
    _query.Execute;
    param.free;
  if return then
  begin
  _query.Active:=false;
  _query.SQL.Clear;
  _query.SQL.Add('select last_insert_rowid()');
  _query.Execute;

  result:=_query.Fields[0].AsInteger;
  end
  else
  result:=0;
end;

procedure TDbHelper.StartTran;
begin
  if _connection.Connected then
    _tran.StartTransaction;
end;

end.
